﻿using System;
using System.ComponentModel;

namespace MyLib.Data {
	/// <summary>
	/// 
	/// </summary>
	[DataObject()]
	public class SysLogProvider : MyLib.DbContextProvider {
		/// <summary>
		/// Create a sys log.
		/// </summary>
		/// <param name="logId"></param>
		/// <param name="operatorId"></param>
		/// <param name="operatorName"></param>
		/// <param name="operation"></param>
		/// <param name="operandId"></param>
		/// <param name="operandName"></param>
		/// <param name="datepoint"></param>
		/// <param name="remark"></param>
		/// <returns></returns>
		[DataObjectMethod(DataObjectMethodType.Insert)]
		public int CreateSysLog(
			out int logId,
			string operatorId,
			string operatorName,
			int operation,
			string operandId,
			string operandName,
			DateTime? datepoint = null,
			string remark = "") {
			var parameterOperatorId = new MyLib.DbParameter("operator_id", MyLib.DbParameterType.String, MyLib.DbParameterMatchType.Exact, 0, System.Data.ParameterDirection.Input, operatorId);
			var parameterOperatorName = new MyLib.DbParameter("operator_name", DbParameterType.String, MyLib.DbParameterMatchType.Exact, 0, System.Data.ParameterDirection.Input, operatorName);
			var parameterOperation = new MyLib.DbParameter("operation", MyLib.DbParameterType.String, MyLib.DbParameterMatchType.Exact, 0, System.Data.ParameterDirection.Input, operation);
			var parameterOperandId = new MyLib.DbParameter("operand_id", MyLib.DbParameterType.String, MyLib.DbParameterMatchType.Exact, 0, System.Data.ParameterDirection.Input, operandId);
			var parameterOperandName = new MyLib.DbParameter("operand_name", DbParameterType.String, MyLib.DbParameterMatchType.Exact, 0, System.Data.ParameterDirection.Input, operandName);
			var parameterDatepoint = new MyLib.DbParameter("datepoint", MyLib.DbParameterType.DateTime, MyLib.DbParameterMatchType.Exact, 0, System.Data.ParameterDirection.Input, datepoint ?? DateTime.Now);
			var parameterRemark = new MyLib.DbParameter("remark", MyLib.DbParameterType.String, MyLib.DbParameterMatchType.Exact, 0, System.Data.ParameterDirection.Input, remark);

			// Create sql.
			var sql1 = @"insert into my_sys_log
			(operator_id, operator_name, operation, operand_id, operand_name, datepoint, remark)
			values(#operator_id#, #operator_name#, #operation#, #operand_id#, #operand_name#, #datepoint#, #remark#)";

			var sql2 = @"select `real_name` from sec_user where `user_id` = #operator_id#";

			var sql3 = @"select last_insert_id() from my_sys_log";

			// Sql text.
			using (var session = SessionFactory.CreateSession()) {
				// Do query.
				if (string.IsNullOrEmpty(operatorName)) {
					parameterOperatorName.Value =
						session.ExecuteScalar<string>(sql2, System.Data.CommandType.Text, parameterOperatorId);
				}

				var c = session.ExecuteUpdate(sql1, System.Data.CommandType.Text,
					parameterOperatorId,
					parameterOperatorName,
					parameterOperation,
					parameterOperandId,
					parameterOperandName,
					parameterDatepoint,
					parameterRemark);

				logId = session.ExecuteScalar<int>(sql3, System.Data.CommandType.Text);

				session.Commit();

				return c;
			}
		} // end of CreateSysLog


		/// <summary>
		/// 
		/// </summary>
		/// <param name="operandName"></param>
		/// <param name="operatorName"></param>
		/// <param name="minDate"></param>
		/// <param name="maxDate"></param>
		/// <param name="maximumRows"></param>
		/// <param name="startRowIndex"></param>
		/// <returns></returns>
		[DataObjectMethod(DataObjectMethodType.Select)]
		public SysLogTable FindSysLog(
			string operandName = "",
			string operatorName = "",
			DateTime? minDate = null,
			DateTime? maxDate = null,
			int maximumRows = int.MaxValue,
			int startRowIndex = 0) {

			ValidationRules.LegalRange("Datepoint", minDate, maxDate);

			if (operandName != null) {
				operandName = operandName.Trim();
			}
			if (operatorName != null) {
				operatorName = operatorName.Trim();
			}

			var parameterOperandName = new MyLib.DbParameter("operand_name", DbParameterType.String, MyLib.DbParameterMatchType.Exact, 0, System.Data.ParameterDirection.Input, operandName);
			var parameterOperatorName = new MyLib.DbParameter("operator_name", DbParameterType.String, MyLib.DbParameterMatchType.Exact, 0, System.Data.ParameterDirection.Input, operatorName);
			var parameterMinDate = new MyLib.DbParameter("min_date", MyLib.DbParameterType.DateTime, MyLib.DbParameterMatchType.Exact, 0, System.Data.ParameterDirection.Input, minDate);
			var parameterMaxDate = new MyLib.DbParameter("max_date", MyLib.DbParameterType.EndDate, MyLib.DbParameterMatchType.Exact, 0, System.Data.ParameterDirection.Input, maxDate);

			// Create sql.

			var sqlBuilder = new System.Text.StringBuilder();
			sqlBuilder.AppendLine(@"select 
			my_log_id as logId, 
			operator_id as operatorId,
			operator_name as operatorName,
			operation,
			operand_id as operandId,
			operand_name as operandName,
			datepoint,
			remark
			from
			my_sys_log");

			// Create dynamic sql 1.

			var dynamicFlag1 = false;
			var dynamicText1 = new System.Text.StringBuilder();

			if (!string.IsNullOrEmpty(operandName)) {
				if (dynamicFlag1)
					dynamicText1.AppendLine(" and ");

				dynamicText1.AppendLine(@"operand_name like concat('%', #operand_name#, '%')");

				dynamicFlag1 = true;
			}
			if (!string.IsNullOrEmpty(operatorName)) {
				if (dynamicFlag1)
					dynamicText1.AppendLine(" and ");

				dynamicText1.AppendLine(@"operator_name like concat('%', #operator_name#, '%')");

				dynamicFlag1 = true;
			}
			if (minDate.HasValue) {
				if (dynamicFlag1)
					dynamicText1.AppendLine(" and ");

				dynamicText1.AppendLine(@"datepoint >= #min_date#");

				dynamicFlag1 = true;
			}
			if (maxDate.HasValue) {
				if (dynamicFlag1)
					dynamicText1.AppendLine(" and ");

				dynamicText1.AppendLine(@"datepoint <= #max_date#");

				dynamicFlag1 = true;
			}

			if (dynamicFlag1) {
				sqlBuilder.Append(" where ");
				sqlBuilder.Append(dynamicText1.ToString());
			}

			sqlBuilder.AppendLine(@"order by datepoint desc");

			// Sql text.

			var sql = sqlBuilder.ToString();

			using (var session = SessionFactory.CreateReadOnlySession()) {

				// Do query.

				var c = new SysLogTable();

				session.ExecutePagedQuery(c, sql, System.Data.CommandType.Text, startRowIndex, maximumRows,
					parameterOperandName,
					parameterOperatorName,
					parameterMinDate,
					parameterMaxDate);

				return c;
			}
		} // end of FindSysLog

		/// <summary>
		/// 
		/// </summary>
		/// <param name="operandName"></param>
		/// <param name="operatorName"></param>
		/// <param name="minDate"></param>
		/// <param name="maxDate"></param>
		/// <returns></returns>
		[DataObjectMethod(DataObjectMethodType.Select)]
		public int FindSysLogCount(
			string operandName = "",
			string operatorName = "",
			DateTime? minDate = null,
			DateTime? maxDate = null) {

			if (operandName != null) {
				operandName = operandName.Trim();
			}
			if (operatorName != null) {
				operatorName = operatorName.Trim();
			}

			var parameterOperandName = new MyLib.DbParameter("operand_name", DbParameterType.String, MyLib.DbParameterMatchType.Exact, 0, System.Data.ParameterDirection.Input, operandName);
			var parameterOperatorName = new MyLib.DbParameter("operator_name", DbParameterType.String, MyLib.DbParameterMatchType.Exact, 0, System.Data.ParameterDirection.Input, operatorName);
			var parameterMinDate = new MyLib.DbParameter("min_date", MyLib.DbParameterType.DateTime, MyLib.DbParameterMatchType.Exact, 0, System.Data.ParameterDirection.Input, minDate);
			var parameterMaxDate = new MyLib.DbParameter("max_date", MyLib.DbParameterType.EndDate, MyLib.DbParameterMatchType.Exact, 0, System.Data.ParameterDirection.Input, maxDate);

			// Create sql.

			var sqlBuilder = new System.Text.StringBuilder();
			sqlBuilder.AppendLine(@"select 
			count(*)			
			from
			my_sys_log");

			// Create dynamic sql 1.

			var dynamicFlag1 = false;
			var dynamicText1 = new System.Text.StringBuilder();

			if (!string.IsNullOrEmpty(operandName)) {
				if (dynamicFlag1)
					dynamicText1.AppendLine(" and ");

				dynamicText1.AppendLine(@"operand_name like concat('%', #operand_name#, '%')");

				dynamicFlag1 = true;
			}
			if (!string.IsNullOrEmpty(operatorName)) {
				if (dynamicFlag1)
					dynamicText1.AppendLine(" and ");

				dynamicText1.AppendLine(@"operator_name like concat('%', #operator_name#, '%')");

				dynamicFlag1 = true;
			}
			if (minDate.HasValue) {
				if (dynamicFlag1)
					dynamicText1.AppendLine(" and ");

				dynamicText1.AppendLine(@"datepoint >= #min_date#");

				dynamicFlag1 = true;
			}
			if (maxDate.HasValue) {
				if (dynamicFlag1)
					dynamicText1.AppendLine(" and ");

				dynamicText1.AppendLine(@"datepoint <= #max_date#");

				dynamicFlag1 = true;
			}

			if (dynamicFlag1) {
				sqlBuilder.Append(" where ");
				sqlBuilder.Append(dynamicText1.ToString());
			}

			// Sql text.

			var sql = sqlBuilder.ToString();

			using (var session = SessionFactory.CreateReadOnlySession()) {

				// Do query.

				var c = session.ExecuteScalar<int>(sql, System.Data.CommandType.Text,
					parameterOperandName,
					parameterOperatorName,
					parameterMinDate,
					parameterMaxDate);

				return c;
			}
		} // end of FindSysLogCount

		/// <summary>
		/// 
		/// </summary>
		/// <param name="id"></param>
		/// <returns></returns>
		[DataObjectMethod(DataObjectMethodType.Select)]
		public SysLogTable FindSysLogById(int id) {
			var parameterId = new DbParameter("my_log_id", DbParameterType.Integer, id);

			using (var session = SessionFactory.CreateReadOnlySession()) {
				var c = new SysLogTable();

				session.ExecuteSingleQuery(
					c,
@"select 
			my_log_id as logId, 
			operator_id as operatorId,
			operator_name as operatorName,
			operation,
			operand_id as operandId,
			operand_name as operandName,
			datepoint,
			remark
			from
			my_sys_log
where my_log_id = #my_log_id#",
					System.Data.CommandType.Text,
					parameterId);

				return c;
			}
		} // end of FindSysLogById.
	} // end of SysLogProvider.
}
